/**
 *
 */
package org.whuims.pois.dbtools;

import org.whuims.pois.common.DBConnection;
import org.whuims.pois.action.result.MainPageBean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author hysk34
 *         <p/>
 *         2010-10-30
 */
public class MainPageTools {


    public List<MainPageBean> execute(String sql) {
        List<MainPageBean> list = new ArrayList<MainPageBean>();
        Connection conn = new DBConnection().getConn();
        Statement st;
        ResultSet rs;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            MainPageBean record;
            while (rs.next()) {
                record = new MainPageBean();
                rsToBean(rs, record);
                list.add(record);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<MainPageBean> executeTopic(String sql) {
        List<MainPageBean> list = new ArrayList<MainPageBean>();
        Connection conn = new DBConnection().getConn();
        Statement st;
        ResultSet rs;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            MainPageBean record;
            while (rs.next()) {
                record = new MainPageBean();
                rsToTopicBean(rs, record);
                list.add(record);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                    conn = null;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    private void rsToBean(ResultSet rs, MainPageBean dbrecord) {
        try {
            dbrecord.setId(rs.getLong("id"));
            dbrecord.setTitleContent(rs.getString("titlecontent"));
            dbrecord.setTitleLink(rs.getString("titlelink"));
            String time = rs.getString("newstime").split(" ")[0];
            dbrecord.setNewsTime(time);

            String abstext = rs.getString("abstext");
            if (abstext.contains("分钟前") || abstext.contains("秒前")) {
                abstext = abstext.substring(abstext.indexOf("分钟前") + 3);
                if (abstext.contains(" - ")) {
                    abstext = abstext.substring(abstext.indexOf(" - ") + 3);
                }
            }
            abstext = abstext.replaceAll("<.*?>", "");
            if (abstext.length() > 75) {
                abstext = abstext.substring(0, 75) + "...";
            }
            dbrecord.setAbsText(abstext);
            dbrecord.setSiteName(rs.getString("sitename"));
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private void rsToTopicBean(ResultSet rs, MainPageBean bean) {
        try {
            bean.setId(rs.getLong("id"));
            bean.setCount(rs.getInt("count"));
            bean.setSimilarId(rs.getString("similarid"));
            String titleContent = rs.getString("titlecontent");
            if (titleContent.length() > 25) {
                titleContent = titleContent.substring(0, 24);
            }
            bean.setTitleContent(titleContent);
            bean.setTitleLink(rs.getString("titlelink"));

            String abstext = rs.getString("abstext");
            if (abstext.contains("分钟前") || abstext.contains("秒前")) {
                if (abstext.contains(" - ")) {
                    abstext = abstext.substring(abstext.indexOf(" - ") + 3);
                }
            }
            abstext = abstext.replaceAll("<.*?>", "");
            if (abstext.length() > 75) {
                abstext = abstext.substring(0, 75) + "...";
            }
            bean.setAbsText(abstext);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
